﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.SqlClient;

namespace EntityExersize
{
    class CustomersManipolations
    {
        public static void UpdateCustomerName(Product updatedProduct) 
        {
            Northwind enteties = new Northwind();
            enteties.Attach(updatedProduct);
            enteties.ObjectStateManager.GetObjectStateEntry(updatedProduct).SetModified();
            enteties.Refresh(System.Data.Objects.RefreshMode.ClientWins, enteties);
            //Product product = enteties.Products.FirstOrDefault(pr => pr.ProductID == productId);
            //product.ProductName = newName;
            enteties.SaveChanges();
        }

        public static int InsertCustomer(string productName) 
        {
            Northwind enteties = new Northwind();
            Product newProduct  = new Product
            {
                ProductName = productName,
                Discontinued = false
            };
            enteties.Products.AddObject(newProduct);
            enteties.SaveChanges();
            return newProduct.ProductID;
        }

        public static void DeleteCustomer(int productId) 
        {
            Northwind enteties = new Northwind();
            Product product = enteties.Products.FirstOrDefault(p => p.ProductID == productId);
            enteties.Products.DeleteObject(product);
            enteties.SaveChanges();

        }

        public static void FindeCustomersPerYearOfOrder(DateTime year) 
        {
            Northwind enteties = new Northwind();
            int i = 0;
            foreach (var customer in enteties.Customers.Include("Orders"))
            {
                if (customer.Orders.FirstOrDefault(o => o.OrderDate.Value.Year == year.Year && o.ShipCountry == "Canada") != null)
                {
                    Console.WriteLine(customer.CompanyName);
                    i++;

                }
                
            }
            Console.WriteLine(i);
        }

        public static void FindeCustomersPerYearOfOrderWithNative(DateTime year)
        {
            Northwind enteties = new Northwind();
            int i = 0;
            string query = "select DISTINCT  c.CompanyName " +
                            "from Customers c " +
                            "join Orders o on o.CustomerID = c.CustomerID " +
                            "where o.ShipCountry='Canada'  and year(o.OrderDate)='1997' " +
                            "group by c.CompanyName , o.OrderDate, o.ShipCountry";
            IEnumerable<string> result = enteties.ExecuteStoreQuery<string>(query);
            foreach (var item in result)
            {
                Console.WriteLine(item);
                i++;
            }
            
            
            Console.WriteLine(i);
        }

        public static void FindSalesByRegionAndPeriod(string region, DateTime start, DateTime end) 
        {
            Northwind enteties = new Northwind();
            foreach (var order in enteties.Orders.Where(o=>o.ShipRegion ==region && o.OrderDate>=start && o.OrderDate<= end))
            {
                Console.WriteLine("date:{0} orderID: {1} region: {2}",order.OrderDate,order.OrderID, order.ShipRegion);
            }
        }

        public static void RecreateNorthwind() 
        {
            Northwind enteties = new Northwind();
            string structure = enteties.CreateDatabaseScript();
            SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS; Database=Northwind; Integrated Security=true");
            SqlCommand create = con.CreateCommand();
            con.Open();
            using (con)
            {
                create.CommandText = "Create Database NortWindTwin ";
                create.ExecuteNonQuery();
                SqlConnection newCon = new SqlConnection(@"Server=.\SQLEXPRESS; Database=NortWindTwin; Integrated Security=true");
                newCon.Open();
                using (newCon)
                {
                    SqlCommand clone = new SqlCommand(structure, newCon);
                    clone.ExecuteNonQuery();
                }
            }
        
        }
    }
}
